﻿using System;
using System.Collections.Generic;
using System.Text;

namespace DusonFramework.DataAccess.Paging
{
    public class Paing_SetRowCount : PagingAbstract
    {
        /*
            DECLARE @TempProducts TABLE
            (
               ID int IDENTITY,
               productid int
            )
            DECLARE @maxRows int
            SET @maxRows = @PageNumber * @PageSize
            --在返回指定的行数之后停止处理查询
            SET ROWCOUNT @maxRows

            INSERT INTO @TempProducts (productid)
            SELECT productid
            FROM products
            ORDER BY productid

            SET ROWCOUNT @PageSize

            SELECT p.*
            FROM @TempProducts t INNER JOIN products p
            ON t.productid = p.productid
            WHERE ID > (@PageNumber - 1) * @PageSize
            SET ROWCOUNT 0
        */
        /// <summary>
        /// 临时表变量方法，用到SET ROWCOUNT
        /// </summary>
        /// <param name="table"></param>
        /// <param name="primaryKey"></param>
        /// <param name="fields"></param>
        /// <param name="filter"></param>
        /// <param name="sortField"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public override string GetPagingSQL(string table, string primaryKey, string fields, string filter, string sortField, int pageIndex, int pageSize)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append("DECLARE @TempProducts TABLE(");
            sql.Append(" ID int IDENTITY, pk int)");

            sql.Append(string.Format(" SET ROWCOUNT {0}", pageIndex * pageSize));

            sql.Append(string.Format(" INSERT INTO @TempProducts (pk)"));
            sql.Append(string.Format(" SELECT {0} FROM {1} ORDER BY {2}", primaryKey, table, sortField));

            sql.Append(string.Format(" SET ROWCOUNT {0}", pageSize));
            sql.Append(string.Format(" SELECT {0} FROM @TempProducts t INNER JOIN {1} ON t.pk = {1}.{2}", fields, table, primaryKey));
            sql.Append(string.Format(" WHERE t.ID > {0}", (pageIndex - 1) * pageSize));
            sql.Append(string.Format(" SET ROWCOUNT 0"));

            return sql.ToString();
        }
    }
}
